﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace UDS.DB.Lib
{
    internal delegate void ExecuteCallback(KeyValuePair<string, string> item);

    internal static class DBUtil
    {
        private static Assembly s_currentAssembly;

        private static Assembly CurrentAssembly
        {
            get
            {
                if (s_currentAssembly == null)
                {
                    s_currentAssembly = Assembly.GetExecutingAssembly();
                }
                return s_currentAssembly;
            }
        }

        /// <summary>
        /// Find all the resources, and return a name-full name map.
        /// </summary>
        internal static IDictionary<string, string> FindEmbededResources(Regex regex)
        {
            string[] resouces = CurrentAssembly.GetManifestResourceNames();
            var map = new SortedDictionary<string, string>(StringComparer.Ordinal);
            foreach (string r in resouces)
            {
                Match match = regex.Match(r);
                if (match.Success)
                {
                    map.Add(match.Groups[1].Value, r);
                }
            }

            return map;
        }

        /// <summary>
        /// Execute all the embeded resources whose names are matched with the specified pattern.
        /// </summary>
        internal static void ExecuteEmbededResources(DBConnection cs, Regex regex)
        {
            ExecuteEmbededResources(cs, regex, kvp => ExecuteEmbededResource(cs, kvp.Value));
        }

        /// <summary>
        /// Execute all the embeded resources whose names are matched with the specified pattern.
        /// </summary>
        internal static void ExecuteEmbededResources(DBConnection cs, Regex regex, ExecuteCallback onExecuting)
        {
            Debug.Assert(cs != null);
            Debug.Assert(regex != null);
            Debug.Assert(onExecuting != null);

            IDictionary<string, string> map = FindEmbededResources(regex);
            Debug.Assert(map != null);

            if (map.Count > 0)
            {
                int index = 0;
                foreach (var kvp in map)
                {
                    Logger.WriteLine(++index, kvp.Key);
                    onExecuting(kvp);
                }
            }
        }

        /// <summary>
        /// Get a list of stored procedures currently created in the database.
        /// </summary>
        internal static IList<string> GetStoredProcedureList(DBConnection cs)
        {
            const string SQL = "SELECT [name] FROM sys.procedures WHERE [name] LIKE 'sproc[_]%';";

            var list = new List<string>(1024);
            using (var dr = cs.ExecuteReader(SQL, null))
            {
                while (dr.Read())
                {
                    list.Add(dr.GetString(0));
                }
            }

            return list;
        }

        internal static void ExecuteEmbededResources(DBConnection cs, string baseDir, IList<string> list)
        {
            if (list != null)
            {
                int index = 0;
                foreach (string name in list)
                {
                    Logger.WriteLine(++index, name);
                    ExecuteEmbededResource(cs, baseDir + '.' + name + ".sql");
                }
            }
        }

        internal static void DropView(DBConnection cs, string viewName)
        {
            VerifyIdentifierName(viewName, true);

            const string sqlFormat = @"IF EXISTS (SELECT * FROM sys.views WHERE [object_id] = OBJECT_ID(N'[dbo].[{0}]')) DROP VIEW [dbo].[{0}];";
            try
            {
                ExecuteSQL(cs, string.Format(sqlFormat, viewName));
            }
            catch (Exception exp)
            {
                throw new InvalidOperationException(string.Format("Failed to drop view: {0}.", viewName), exp);
            }
        }

        internal static void DropTable(DBConnection cs, string tableName)
        {
            VerifyIdentifierName(tableName, true);

            const string sqlFormat = @"IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) DROP TABLE [dbo].[{0}];";
            try
            {
                ExecuteSQL(cs, string.Format(sqlFormat, tableName));
            }
            catch (Exception exp)
            {
                throw new InvalidOperationException(string.Format("Failed to drop table: {0}.", tableName), exp);
            }
        }

        internal static void DropStoredProcedure(DBConnection cs, string spName)
        {
            VerifyIdentifierName(spName, true);

            const string sqlFormat = @"IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[{0}];";
            try
            {
                ExecuteSQL(cs, string.Format(sqlFormat, spName));
            }
            catch (Exception exp)
            {
                throw new InvalidOperationException(string.Format("Failed to drop stored procedure: {0}.", spName), exp);
            }
        }

        internal static void DropFunction(DBConnection cs, string funcName)
        {
            VerifyIdentifierName(funcName, true);

            const string sqlFormat = @"IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[{0}];";
            try
            {
                ExecuteSQL(cs, string.Format(sqlFormat, funcName));
            }
            catch (Exception exp)
            {
                throw new InvalidOperationException(string.Format("Failed to drop function: {0}.", funcName), exp);
            }
        }

        internal static void ExecuteSQL(DBConnection connection, string sql)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentNullException("sql");
            }

            var goRegex = new Regex(@"^\s*GO\s*$", RegexOptions.Compiled | RegexOptions.IgnoreCase);

            // Split the SQL command to several sub commands which are sperated by a GO statement.
            var list = new List<string>();

            // By default, we'll set the following optoins for sql scripts
            list.Add("SET ANSI_NULLS ON");
            list.Add("SET QUOTED_IDENTIFIER OFF");

            using (var sr = new StringReader(sql))
            {
                var sb = new StringBuilder();
                while (true)
                {
                    string line = sr.ReadLine();
                    if (line == null)
                    {
                        // The end of the sql command
                        list.Add(sb.ToString());
                        break;
                    }

                    if (goRegex.IsMatch(line))
                    {
                        list.Add(sb.ToString());
                        sb = new StringBuilder();
                    }
                    else
                    {
                        sb.AppendLine(line);
                    }
                }
            }

            foreach (string subSql in list)
            {
                if (!string.IsNullOrEmpty(subSql))
                {
                    connection.ExecuteSQL(subSql, null);
                }
            }
        }

        /// <summary>
        /// Checks if the specified name is valid for a SQL identifier.
        /// </summary>
        internal static bool VerifyIdentifierName(string name, bool throwException)
        {
            if (string.IsNullOrEmpty(name))
            {
                // Cannot be empty.
                goto INVALID_IDENTIFIER;
            }

            char firstChar = name[0];
            if (firstChar != '_'
                && !('a' <= firstChar && firstChar <= 'z')
                    && !('A' <= firstChar && firstChar <= 'Z'))
            {
                // The first character must be: a-z, A-Z, _
                goto INVALID_IDENTIFIER;
            }

            for (int i = 1; i < name.Length; i++)
            {
                char ch = name[i];
                if (ch == '_')
                {
                    continue;
                }

                if ('0' <= ch && ch <= '9')
                {
                    continue;
                }

                if ('a' <= ch && ch <= 'z')
                {
                    continue;
                }

                if ('A' <= ch && ch <= 'Z')
                {
                    continue;
                }

                goto INVALID_IDENTIFIER;
            }

            return true;

        INVALID_IDENTIFIER:
            if (throwException)
            {
                throw new ArgumentException(string.Format("'{0}' is not a valid SQL identifier.", name), "name");
            }
            return false;
        }

        internal static void DropAllStoredProcedures(DBConnection sqlConnection)
        {
            DropAllStoredProcedures(sqlConnection, DateTime.MaxValue);
        }

        internal static void DropAllStoredProcedures(DBConnection sqlConnection, DateTime maxCreateDate)
        {
            Logger.WriteLine();
            Logger.WriteLine("Dropping legacy stored procedures...");

            string sql = "SELECT [name] FROM sys.procedures WHERE [name] LIKE 'sproc_%' ORDER BY [name] asc";
            IList<SqlParameter> parameters = null;
            if (maxCreateDate != DateTime.MaxValue)
            {
                sql = "SELECT [name] FROM sys.procedures WHERE ([create_date] <= @max_date) AND ([name] LIKE 'sproc_%') ORDER BY [name] asc";

                var p = new SqlParameter("@max_date", SqlDbType.DateTime);
                p.Value = (maxCreateDate > SqlDateTime.MaxValue.Value) ? SqlDateTime.MaxValue.Value : maxCreateDate;

                parameters = new List<SqlParameter>(1);
                parameters.Add(p);
            }

            // Get all the stored procedures to remove.
            var list = new List<string>();
            using (var dr = sqlConnection.ExecuteReader(sql, parameters))
            {
                while (dr.Read())
                {
                    list.Add(dr.GetString(0));
                }
            }

            // Remove them now.
            for (int i = 0; i < list.Count; i++)
            {
                Logger.WriteLine(i + 1, list[i]);
                DBUtil.DropStoredProcedure(sqlConnection, list[i]);
            }
        }

        internal static DateTime GetCurrentDBTime(DBConnection connection)
        {
            using (var dr = connection.ExecuteReader("SELECT getdate();", null))
            {
                dr.Read();
                return dr.GetDateTime(0);

            }
        }

        internal static void VerifyNetworkServiceLogin(SqlConnectionStringBuilder csBuilder)
        {
            string dbName = csBuilder.InitialCatalog;
            DBUtil.VerifyIdentifierName(dbName, true);
            const string CREATE_LOGIN = @"
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'NT AUTHORITY\NETWORK SERVICE')
BEGIN
    CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS WITH DEFAULT_DATABASE=[{0}]
END";
            const string CREATE_USER = @"
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'NT AUTHORITY\NETWORK SERVICE')
BEGIN
    CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] WITH DEFAULT_SCHEMA=[dbo]
END";
            const string ADD_TO_DBOWNER = @"
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_role_members
    WHERE
        role_principal_id IN (SELECT principal_id FROM sys.database_principals WHERE [name] = N'db_owner' AND [type] = 'R')
        AND member_principal_id IN (SELECT principal_id FROM sys.database_principals WHERE [name] = N'NT AUTHORITY\NETWORK SERVICE' AND [type] = 'U')
)
BEGIN
    exec sp_addrolemember @rolename = N'db_owner', @membername = N'NT AUTHORITY\NETWORK SERVICE';
END
";

            using (var connection = new DBConnection(csBuilder.ConnectionString))
            {
                connection.Open();
                connection.BeginTransaction();

                connection.ExecuteSQL(string.Format(CREATE_LOGIN, dbName), null);
                connection.ExecuteSQL(CREATE_USER, null);
                connection.ExecuteSQL(ADD_TO_DBOWNER, null);

                connection.CommitTransaction();
            }
        }

        internal static void ExecuteEmbededResource(DBConnection cs, string resourceName)
        {
            Encoding defaultEncoding = Encoding.GetEncoding(936); // gb-2312
            using (Stream stream = CurrentAssembly.GetManifestResourceStream(resourceName))
            {
                if (stream == null)
                {
                    throw new InvalidOperationException(string.Format("The embeded resource '{0}' cannot be found!", resourceName));
                }

                using (var sr = new StreamReader(stream, defaultEncoding, true))
                {
                    ExecuteSQL(cs, sr.ReadToEnd());
                }
            }
        }
    }
}